package com.bky.jdbc;

import org.apache.log4j.Logger;

import java.sql.*;
import java.util.Calendar;

/**
 * Created by shipeilei on 2017/5/26.
 */
public class SQLUtil {
    private static Logger logger = Logger.getLogger(SQLUtil.class);

    /**
     * 将参数绑定到PreparedStatement中
     * @param ps
     * @param types
     * @param params
     * @throws SQLException
     */
    public static void bindParameters(PreparedStatement ps, int[] types, Object[] params) throws SQLException {
        if(types == null)
            return;
        for (int i = 1; i <= types.length; i++) {
            bindParameter(ps, i, types[i - 1], params[i - 1]);
        }
    }

    /**
     * 将参数绑定到PreparedStatement中
     * @param pstmt
     * @param index
     * @param type
     * @param param
     * @throws SQLException
     */
    private static void bindParameter(PreparedStatement pstmt, int index, int type, Object param) throws SQLException {
        if (param == null) {
            if (type == Integer.MIN_VALUE) {
                pstmt.setObject(index, null);
            } else {
                pstmt.setNull(index, type);
            }
        } else {
            if (type == Types.VARCHAR) {
                pstmt.setString(index, param.toString());
            } else if(type == Types.INTEGER){
                if(param == null || param.toString().trim().equals("")){
                    pstmt.setNull(index, Types.INTEGER);
                    return;
                }
                try{
                    pstmt.setInt(index, Integer.parseInt(param.toString()));
                }catch(NumberFormatException e){
                    double temp = Double.parseDouble(param.toString());
                    if(temp % 1 != 0){
                        throw e;
                    }
                    pstmt.setInt(index, (int)temp);
                }
            } else if (type == Types.DATE) {
                if (param instanceof java.util.Date) {
                    if (param instanceof java.sql.Date) {
                        pstmt.setDate(index, (java.sql.Date) param);
                    } else {
                        pstmt.setDate(index, new java.sql.Date(((java.util.Date) param).getTime()));
                    }
                } else if (param instanceof Calendar) {
                    Calendar cal = (Calendar) param;
                    pstmt.setDate(index, new java.sql.Date(cal.getTime().getTime()), cal);
                } else {
                    pstmt.setObject(index, param, Types.DATE);
                }
            } else if (type == Types.TIME) {
                if (param instanceof java.util.Date) {
                    if (param instanceof Time) {
                        pstmt.setTime(index, (Time) param);
                    } else {
                        pstmt.setTime(index, new Time(((java.util.Date) param).getTime()));
                    }
                } else if (param instanceof Calendar) {
                    Calendar cal = (Calendar) param;
                    pstmt.setTime(index, new Time(cal.getTime().getTime()), cal);
                } else {
                    pstmt.setObject(index, param, Types.TIME);
                }
            } else if (type == Types.TIMESTAMP) {
                if (param instanceof java.util.Date) {
                    if (param instanceof Timestamp) {
                        pstmt.setTimestamp(index, (Timestamp) param);
                    } else {
                        pstmt.setTimestamp(index, new Timestamp(((java.util.Date) param).getTime()));
                    }
                } else if (param instanceof Calendar) {
                    Calendar cal = (Calendar) param;
                    pstmt.setTimestamp(index, new Timestamp(cal.getTime().getTime()), cal);
                } else {
                    pstmt.setObject(index, param, Types.TIMESTAMP);
                }
            } else if(type == Types.DOUBLE){
                if(param == null || param.toString().trim().equals("")){
                    pstmt.setNull(index, Types.DOUBLE);
                    return;
                }
                pstmt.setDouble(index, Double.parseDouble(param.toString()));
            }else if (type == Integer.MIN_VALUE) {
                if ((param instanceof java.util.Date)
                        && !(param instanceof java.sql.Date || param instanceof Time || param instanceof Timestamp)) {
                    pstmt.setObject(index, new Timestamp(((java.util.Date) param).getTime()));
                } else if (param instanceof Calendar) {
                    pstmt.setObject(index, new Timestamp(((Calendar) param).getTime().getTime()));
                } else {
                    pstmt.setObject(index, param);
                }
            } else {
                pstmt.setObject(index, param, type);
            }
        }
    }


    /**
     * 参数个数验证
     * @param sql
     * @param types
     * @param params
     * @return
     */
    public static boolean checkParam(String sql,int[] types,Object[] params){
        int paramCount = 0;
        for (int idx = 0; idx < sql.length(); idx++) {
            idx = sql.indexOf("?", idx);
            if (idx == -1) {
                break;
            }
            else {
                paramCount++;
            }
        }
        if(paramCount == 0)
            return true;
        if(types == null || params == null){
            logger.error("SQL需要传参个数为:" + paramCount + ",但是给定的参数为空");
            return false;
        }
        if(paramCount != types.length || paramCount != params.length){
            logger.error("SQL需要传参个数为:" + paramCount + ",但是给定的参数类型个数:" + types.length + ",参数个数为：" + params.length);
            return false;
        }
        return true;
    }

    /**
     * 参数类型初步验证
     * @param types
     * @param params
     */
    public static boolean checkParamType(int[] types, Object[] params) {
        if(types == null || params == null){
            logger.error("验证传参不能为空！！！！");
            return false;
        }
        if(types.length != params.length){
            logger.error("验证传参不正确！类型期望值为：" + types.length + "，实际值为：" + params.length);
            return false;
        }
        try{
            for(int i = 0;i<types.length;i++){
                int type = types[i];
                Object param = params[i];
                if(param == null || param.toString().trim().equals("")){
                    continue;
                }
                if(type == Types.INTEGER){
                    Integer.parseInt(param.toString().trim());
                } else if (type == Types.DATE || type == Types.TIME || type == Types.TIMESTAMP) {
                    if (!(param instanceof java.util.Date)) {
                        return false;
                    }
                } else if(type == Types.DOUBLE){
                    Double.parseDouble(param.toString().trim());
                }
            }
        }catch(Exception e){
            logger.error("验证传参信息：" + e.getMessage());
            return false;
        }
        return true;
    }
}
